
--USE Griferos
--GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerColaboradorPorId')
BEGIN
	DROP PROCEDURE ObtenerColaboradorPorId;
END 
GO

CREATE PROCEDURE ObtenerColaboradorPorId
	@IdColaborador INTEGER
AS
BEGIN
	SELECT IdColaborador,
		   DocumentoIdentidad,
		   CASE TipoDocumento WHEN 'D' THEN 'DNI' ELSE '' END AS TipoDocumento,
		   Nombres,
		   Apellidos,
		   Telefono,
		   Domicilio,
		   Estado
	  FROM Colaborador
	 WHERE IdColaborador = @IdColaborador;
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerColaboradorPorDocumento')
BEGIN
	DROP PROCEDURE ObtenerColaboradorPorDocumento;
END 
GO


CREATE PROCEDURE ObtenerColaboradorPorDocumento
	@Documento INTEGER
AS
BEGIN
	SELECT IdColaborador,
		   DocumentoIdentidad,
		   CASE TipoDocumento WHEN 'D' THEN 'DNI' ELSE '' END AS TipoDocumento,
		   Nombres,
		   Apellidos,
		   Telefono,
		   Domicilio,
		   Estado
	  FROM Colaborador
	 WHERE DocumentoIdentidad = @Documento;
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerColaboradores')
BEGIN
	DROP PROCEDURE ObtenerColaboradores;
END 
GO


CREATE PROCEDURE ObtenerColaboradores
	@Estado CHAR
AS
BEGIN
	SELECT IdColaborador,
		   DocumentoIdentidad,
		   CASE TipoDocumento WHEN 'D' THEN 'DNI' ELSE '' END AS TipoDocumento,
		   Nombres,
		   Apellidos,
		   Telefono,
		   Domicilio,
		   Estado
	  FROM Colaborador
	 WHERE Estado = @Estado;
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarEditarColaborador')
BEGIN
	DROP PROCEDURE InsertarEditarColaborador;
END 
GO


CREATE PROCEDURE InsertarEditarColaborador
	@IdColaborador INTEGER,
	@DocumentoIdentidad INTEGER,
	@TipoDocumento CHAR,
	@Nombres VARCHAR(100),
	@Apellidos VARCHAR(100),
	@Telefono VARCHAR(20),
	@Domicilio VARCHAR(500)
AS
BEGIN
	
	UPDATE Colaborador
	   SET DocumentoIdentidad = @DocumentoIdentidad, 
		   TipoDocumento = @TipoDocumento, 
		   Nombres = @Nombres, 
		   Apellidos = @Apellidos, 
		   Telefono = @Telefono, 
		   Domicilio = @Domicilio
	 WHERE IdColaborador = @IdColaborador;
	
	IF @@ROWCOUNT = 0
	BEGIN
	
		INSERT INTO Colaborador (DocumentoIdentidad, TipoDocumento, Nombres, Apellidos, Telefono, Domicilio, Estado)
			VALUES(@DocumentoIdentidad, @TipoDocumento, @Nombres, @Apellidos, @Telefono, @Domicilio, 'A');
	
	END
	
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerFaltantesSobrantes')
BEGIN
	DROP PROCEDURE ObtenerFaltantesSobrantes;
END 
GO


CREATE PROCEDURE ObtenerFaltantesSobrantes 
	@Fecha DATE
AS
BEGIN

	SELECT COL.IdColaborador, 
		   COL.Nombres + ' ' + COL.Apellidos AS Nombres,
		   FS.FechaIngreso,
		   FS.Faltante,
		   FS.Sobrante
	  FROM Colaborador COL
	  LEFT JOIN ( SELECT IdColaborador,
						 FechaIngreso,
						 Faltante,
						 Sobrante
				    FROM FaltantesSobrantes
				   WHERE FechaIngreso = @Fecha ) FS
	    ON COL.IdColaborador = FS.IdColaborador
     WHERE COL.Estado = 'A';

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarActualizarFaltantesSobrantes')
BEGIN
	DROP PROCEDURE InsertarActualizarFaltantesSobrantes;
END 
GO


CREATE PROCEDURE InsertarActualizarFaltantesSobrantes
	@IdColaborador INTEGER,
	@FechaIngreso DATE,
	@Faltante NUMERIC(10, 2),
	@Sobrante NUMERIC(10, 2)
AS
BEGIN

	UPDATE FaltantesSobrantes 
	   SET Faltante = @Faltante, 
		   Sobrante = @Sobrante
	 WHERE FechaIngreso = @FechaIngreso
	   AND IdColaborador = @IdColaborador;
	
	IF @@ROWCOUNT = 0
	BEGIN

		INSERT INTO FaltantesSobrantes (IdColaborador, FechaIngreso, Faltante, Sobrante)
			VALUES(@IdColaborador, @FechaIngreso, @Faltante, @Sobrante);
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerVentas')
BEGIN
	DROP PROCEDURE ObtenerVentas;
END 
GO


CREATE PROCEDURE ObtenerVentas
	@Anio INTEGER,
	@IdMes INTEGER
AS
BEGIN

	SELECT COL.IdColaborador, 
		   COL.Nombres + ' ' + COL.Apellidos AS Nombres,
		   VEN.Anio,
		   VEN.IdMes,
		   VEN.VentasContado,
		   VEN.VentasCredito
	  FROM Colaborador COL
	  LEFT JOIN ( SELECT IdColaborador,
						 Anio,
						 IdMes,
						 VentasContado,
						 VentasCredito
				    FROM Ventas
				   WHERE IdMes = @IdMes 
				     AND Anio = @Anio) VEN
	    ON COL.IdColaborador = VEN.IdColaborador
     WHERE COL.Estado = 'A';

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarActualizarVentas')
BEGIN
	DROP PROCEDURE InsertarActualizarVentas;
END 
GO


CREATE PROCEDURE InsertarActualizarVentas
	@IdColaborador INTEGER,
	@Anio INTEGER,
	@IdMes INTEGER,
	@VentasContado NUMERIC(10, 2),
	@VentasCredito NUMERIC(10, 2)
AS
BEGIN

	UPDATE Ventas 
	   SET VentasContado = @VentasContado,
		   VentasCredito = @VentasCredito
	 WHERE IdColaborador = @IdColaborador
	   AND IdMes = @IdMes
	   AND Anio = @Anio;
	
	IF @@ROWCOUNT = 0
	BEGIN

		INSERT INTO Ventas (IdColaborador, Anio, IdMes, VentasContado, VentasCredito)
			VALUES(@IdColaborador, @Anio, @IdMes, @VentasContado, @VentasCredito);
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerMeses')
BEGIN
	DROP PROCEDURE ObtenerMeses;
END 
GO


CREATE PROCEDURE ObtenerMeses
AS
BEGIN

	SELECT IdMes,
		   NombreMes
	  FROM Mes;

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerErroresPorColaborador')
BEGIN
	DROP PROCEDURE ObtenerErroresPorColaborador;
END 
GO


CREATE PROCEDURE ObtenerErroresPorColaborador
	@IdColaborador INTEGER,
	@FechaIngreso DATE
AS
BEGIN


	SELECT ERR.IdError, 
		   ERR.NombreError,
		   @IdColaborador AS IdColaborador,
		   COL.Cantidad,
		   COL.Observaciones,
		   COL.FechaIngreso
	  FROM Errores ERR
	  LEFT JOIN ( SELECT IdError,
						 Cantidad,
						 Observaciones,
						 FechaIngreso
				    FROM ErroresPorColaborador 
				   WHERE IdColaborador = @IdColaborador 
				     AND FechaIngreso = @FechaIngreso ) COL
	    ON COL.IdError = ERR.IdError
	 WHERE ERR.Estado = 'A';

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarActualizarErrores')
BEGIN
	DROP PROCEDURE InsertarActualizarErrores;
END 
GO


CREATE PROCEDURE InsertarActualizarErrores 
	@IdError  INTEGER,
	@IdColaborador  INTEGER,
	@FechaIngreso DATE,
	@Cantidad INTEGER,
	@Observaciones VARCHAR
AS
BEGIN

	UPDATE ErroresPorColaborador
	   SET Cantidad = @Cantidad,
		   Observaciones = @Observaciones
	 WHERE IdColaborador = @IdColaborador
	   AND FechaIngreso = @FechaIngreso
	   AND IdError = @IdError;
	
	IF @@ROWCOUNT = 0
	BEGIN

		INSERT INTO ErroresPorColaborador (IdColaborador, IdError, FechaIngreso, Cantidad, Observaciones)
			VALUES (@IdColaborador, @IdError, @FechaIngreso, @Cantidad, @Observaciones);
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerReporteFaltante')
BEGIN
	DROP PROCEDURE ObtenerReporteFaltante;
END 
GO

CREATE PROCEDURE ObtenerReporteFaltante 
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
		@TEXTO_JSON VARCHAR(MAX) = '';

DECLARE @FaltantesPorMes
	TABLE (
		IdMes INT,
		IdColaborador INT,
		Faltante NUMERIC(10, 2),
		Nombres VARCHAR(100)
	);

BEGIN

	IF EXISTS ( SELECT 1 
				  FROM FaltantesSobrantes 
				 WHERE MONTH(FechaIngreso) BETWEEN @MesInicio AND @MesFin
				   AND YEAR(FechaIngreso) = @Anio )
	BEGIN
	
		INSERT INTO @FaltantesPorMes
			 SELECT MONTH(FechaIngreso) AS IdMes,
					COL.IdColaborador,
					SUM(Faltante) AS Faltante,
					MAX(COL.Nombres) AS Nombres
			   FROM Colaborador COL
			   LEFT JOIN FaltantesSobrantes FS
				 ON FS.IdColaborador = COL.IdColaborador
			  WHERE YEAR(FechaIngreso) = @Anio
				AND COL.Estado = 'A'
				AND MONTH(FechaIngreso) BETWEEN @MesInicio AND @MesFin
			  GROUP BY COL.IdColaborador, MONTH(FechaIngreso);
	
		INSERT INTO @FaltantesPorMes
			SELECT IdMes, IdColaborador, 0, ( SELECT Nombres FROM Colaborador C WHERE C.IdColaborador = Origen.IdColaborador)
			  FROM ( SELECT Mes.IdMes, COL.IdColaborador
					   FROM Mes, Colaborador COL
					  WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin
						AND COL.Estado = 'A'

					 EXCEPT
							
					 SELECT IdMes, IdColaborador
					   FROM @FaltantesPorMes ) AS Origen;

	
		WITH DataPoints
		AS
		( 
			 SELECT '{ y: ' + CAST(SUM(Faltante) AS VARCHAR) + ', label: ''' + 
					MAX(Nombres) + ''' }' AS Texto,
					IdColaborador,
					IdMes
			   FROM @FaltantesPorMes
			  GROUP BY IdColaborador, IdMes
	   )

		SELECT @TEXTO_JSON = @TEXTO_JSON + 
			   '{ type: ''stackedColumn'', toolTipContent: "{label}<br/><span style=''color: {color};''><strong>{name}</strong></span>: S/. {y}", ' +
			   'name: ''' + Mes.NombreMes + ''', showInLegend: ''false'', dataPoints: [' +
			   SUBSTRING(DAT.Texto, 1, LEN(DAT.Texto) - 1) + ' ] }, '
		  FROM ( SELECT DISTINCT IdMes
				   FROM DataPoints ) M
			   CROSS APPLY
			   ( SELECT Texto + ', ' 
				   FROM DataPoints AS D 
				  WHERE M.IdMes = D.IdMes
					FOR XML PATH('') ) DAT (Texto)
		  JOIN Mes
			ON Mes.IdMes = M.IdMes
		 WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin;


		SET @TEXTO_JSON = @TEXTO_JSON +' { type: "line", dataPoints: [ ';


		WITH FaltantesPorMes 
		AS
		(
			 SELECT SUM(FSM.Faltante) AS Resta,
					MAX(FSM.Nombres) AS Nombre,
					Mes.IdMes,
					FSM.IdColaborador
			   FROM @FaltantesPorMes FSM
			   JOIN Mes
				 ON Mes.IdMes = FSM.IdMes
			  WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin
			  GROUP BY FSM.IdColaborador, Mes.IdMes
		)

		 SELECT @TEXTO_JSON = @TEXTO_JSON + 
				'{ y: ' + CAST( SUM(Resta) AS VARCHAR ) +
				', label: ''' + MAX(Nombre) + ''' }, '
		   FROM FaltantesPorMes
		  GROUP BY IdColaborador;

		SELECT '{ theme: ''theme2'', title:{ text: '''' }, data: [ ' +
			   SUBSTRING(@TEXTO_JSON, 1, LEN(@TEXTO_JSON) - 1) + ' ] } ] }';
	
	END
	ELSE
	BEGIN
	
		SELECT @TEXTO_JSON;
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerResumenDeFaltantes')
BEGIN
	DROP PROCEDURE ObtenerResumenDeFaltantes;
END 
GO


CREATE PROCEDURE ObtenerResumenDeFaltantes
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #FALTANTES_PIVOT 
(
	IdColaborador INT,
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);

BEGIN

	INSERT INTO #FALTANTES_PIVOT
		SELECT IdColaborador, [1] AS Enero, [2] AS Febrero, 
			   [3] AS Marzo, [4] AS Abril, [5] AS Mayo, 
			   [6] AS Junio, [7] AS Julio, [8] AS Agosto, 
			   [9] AS Setiembre, [10] AS Octubre, 
			   [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT IdColaborador, 
						MONTH(FechaIngreso) AS MesIngreso, 
						SUM(Sobrante) - SUM(Faltante) AS Faltante
				   FROM FaltantesSobrantes
				   JOIN Mes 
					 ON MONTH(FechaIngreso) = Mes.IdMes
				  WHERE MONTH(FechaIngreso) BETWEEN @MesInicio AND @MesFin
				    AND YEAR(FechaIngreso) = @Anio
				  GROUP BY MONTH(FechaIngreso), IdColaborador ) AS TablaOrigen
		 PIVOT ( SUM(Faltante) FOR MesIngreso 
						IN ([1], [2], [3], [4], [5], [6], 
							[7], [8], [9], [10], [11], [12] ) ) AS TablaPivot;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #FALTANTES_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT COL.Nombres AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #FALTANTES_PIVOT PVT ' +
						' JOIN Colaborador COL ' +
						'   ON PVT.IdColaborador = COL.IdColaborador ';
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SELECT IdColaborador 
		  FROM #FALTANTES_PIVOT;
	END

	DROP TABLE #FALTANTES_PIVOT

END
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InactivarColaborador')
BEGIN
	DROP PROCEDURE InactivarColaborador;
END 
GO


CREATE PROCEDURE InactivarColaborador
	@IdColaborador INTEGER
AS
BEGIN
	UPDATE Colaborador SET Estado = 'I' WHERE IdColaborador = @IdColaborador;
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ActivarColaborador')
BEGIN
	DROP PROCEDURE ActivarColaborador;
END 
GO


CREATE PROCEDURE ActivarColaborador
	@IdColaborador INTEGER
AS
BEGIN
	UPDATE Colaborador SET Estado = 'A' WHERE IdColaborador = @IdColaborador;
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerErrores')
BEGIN
	DROP PROCEDURE ObtenerErrores;
END 
GO


CREATE PROCEDURE ObtenerErrores
AS
BEGIN

	SELECT IdError,
		   NombreError,
		   Estado
	  FROM Errores;
	
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarActualizarError')
BEGIN
	DROP PROCEDURE InsertarActualizarError;
END 
GO


CREATE PROCEDURE InsertarActualizarError
	@IdError INTEGER,
	@NombreError VARCHAR(500),
	@Estado CHAR
AS
BEGIN

	UPDATE Errores
	   SET NombreError = @NombreError,
		   Estado = @Estado
	 WHERE IdError = @IdError;
	 
	IF (@@ROWCOUNT = 0)
	BEGIN
		
		INSERT INTO Errores (NombreError, Estado)
				values (@NombreError, @Estado);
		
	END
	
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ActivarInactivarError')
BEGIN
	DROP PROCEDURE ActivarInactivarError;
END 
GO


CREATE PROCEDURE ActivarInactivarError
	@IdError INTEGER
AS
BEGIN
	
	UPDATE Errores
	   SET Estado = (SELECT CASE Estado 
								WHEN 'I' THEN 'A'
								WHEN 'A' THEN 'I'
							END
					   FROM Errores
					  WHERE IdError = @IdError)
	 WHERE IdError = @IdError;
	
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerErrorPorId')
BEGIN
	DROP PROCEDURE ObtenerErrorPorId;
END 
GO


CREATE PROCEDURE ObtenerErrorPorId
	@IdError INTEGER
AS
BEGIN

	SELECT IdError,
		   NombreError,
		   Estado
	  FROM Errores
	 WHERE IdError = @IdError;
	 
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerReporteVentasAlContado')
BEGIN
	DROP PROCEDURE ObtenerReporteVentasAlContado;
END 
GO

CREATE PROCEDURE ObtenerReporteVentasAlContado
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@TEXTO_JSON VARCHAR(MAX) = '';

BEGIN

	IF EXISTS ( SELECT 1 
				  FROM Ventas
				 WHERE IdMes BETWEEN @MesInicio AND @MesFin
				   AND Anio = @Anio)
	BEGIN
	
		WITH DataPoints
		AS ( SELECT '{ y: ' + CAST(SUM(VentasContado) AS VARCHAR) + ', label: ''' + 
						MAX(COL.Nombres) + ''' }' AS Texto,
						COL.IdColaborador,
						VEN.IdMes
				   FROM Ventas VEN
				   JOIN Colaborador COL
					 ON VEN.IdColaborador = COL.IdColaborador
				  WHERE VEN.Anio = @Anio
					AND COL.Estado = 'A'
				  GROUP BY COL.IdColaborador, VEN.IdMes 
			   )

		SELECT @TEXTO_JSON = @TEXTO_JSON + 
			   '{ type: ''stackedColumn'', toolTipContent: "{label}<br/><span style=''color: {color};''><strong>{name}</strong></span>: S/. {y}", ' +
			   'name: ''' + Mes.NombreMes + ''', showInLegend: ''false'', dataPoints: [' +
			   SUBSTRING(DAT.Texto, 1, LEN(DAT.Texto) - 1) + ' ] }, '
		  FROM ( SELECT DISTINCT IdMes
				   FROM DataPoints ) M
			   CROSS APPLY
			   ( SELECT Texto + ', ' 
				   FROM DataPoints AS D 
				  WHERE M.IdMes = D.IdMes
					FOR XML PATH('') ) DAT (Texto)
		  JOIN Mes
			ON Mes.IdMes = M.IdMes
		 WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin;


		SET @TEXTO_JSON = @TEXTO_JSON +' { type: "line", dataPoints: [ ';


		WITH VentasPorMes 
		AS
		(
			 SELECT SUM(VEN.VentasContado) AS Resta,
					MAX(COL.Nombres) AS Nombre,
					Mes.IdMes,
					COL.IdColaborador
			   FROM Ventas VEN
			   JOIN Colaborador COL
				 ON VEN.IdColaborador = COL.IdColaborador
			   JOIN Mes
				 ON Mes.IdMes = VEN.IdMes
			  WHERE Anio = @Anio
				AND COL.Estado = 'A'
				AND Mes.IdMes BETWEEN @MesInicio AND @MesFin
			  GROUP BY COL.IdColaborador, Mes.IdMes
		)

		 SELECT @TEXTO_JSON = @TEXTO_JSON + 
				'{ y: ' + CAST( SUM(Resta) AS VARCHAR ) +
				', label: ''' + MAX(Nombre) + ''' }, '
		   FROM VentasPorMes 
		  WHERE Resta > 0
		  GROUP BY IdColaborador;
		
		SELECT '{ theme: ''theme2'', title:{ text: '''' }, data: [ ' +
			   SUBSTRING(@TEXTO_JSON, 1, LEN(@TEXTO_JSON) - 1) + ' ] } ] }';
	
	END
	ELSE
	BEGIN
	
		SELECT @TEXTO_JSON;
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerResumenDeVentas')
BEGIN
	DROP PROCEDURE ObtenerResumenDeVentas;
END 
GO


CREATE PROCEDURE ObtenerResumenDeVentas
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #VENTAS_PIVOT 
(
	IdColaborador INT,
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);

BEGIN

	INSERT INTO #VENTAS_PIVOT 
		SELECT IdColaborador, [1] AS Enero, [2] AS Febrero, 
			   [3] AS Marzo, [4] AS Abril, [5] AS Mayo, 
			   [6] AS Junio, [7] AS Julio, [8] AS Agosto, 
			   [9] AS Setiembre, [10] AS Octubre, 
			   [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT IdColaborador, 
						VEN.IdMes AS MesIngreso, 
						SUM(VentasContado) AS VentasContado
				   FROM Ventas VEN
				   JOIN Mes 
					 ON VEN.IdMes = Mes.IdMes
				  WHERE VEN.IdMes BETWEEN @MesInicio AND @MesFin
				  GROUP BY VEN.IdMes, IdColaborador ) AS TablaOrigen
		 PIVOT ( SUM(VentasContado) FOR MesIngreso 
						IN ([1], [2], [3], [4], [5], [6], 
							[7], [8], [9], [10], [11], [12] ) ) AS TablaPivot;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #VENTAS_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT COL.Nombres AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #FALTANTES_PIVOT PVT ' +
						' JOIN Colaborador COL ' +
						'   ON PVT.IdColaborador = COL.IdColaborador ';
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SELECT IdColaborador 
		  FROM #VENTAS_PIVOT;
	END

	DROP TABLE #VENTAS_PIVOT; 

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerVentasPorGrifero')
BEGIN
	DROP PROCEDURE ObtenerVentasPorGrifero;
END 
GO


CREATE PROCEDURE ObtenerVentasPorGrifero
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #VENTAS_PIVOT 
(
	Colaborador VARCHAR(500),
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);


BEGIN

	INSERT INTO #VENTAS_PIVOT
		SELECT Nombres, [1] AS Enero, [2] AS Febrero, [3] AS Marzo, 
						[4] AS Abril, [5] AS Mayo, [6] AS Junio, 
						[7] AS Julio, [8] AS Agosto, [9] AS Setiembre,
						[10] AS Octubre, [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT C.IdColaborador, Nombres, M.IdMes,
						VentasContado + VentasCredito AS Ventas
				   FROM Colaborador C
				   JOIN Ventas V
					 ON V.IdColaborador = C.IdColaborador
				   JOIN Mes M
					 ON V.IdMes = M.IdMes
				  WHERE V.IdMes BETWEEN @MesInicio AND @MesFin
				    AND Estado = 'A' ) COL
		 PIVOT ( SUM(Ventas) 
				   FOR IdMes IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) ) AS pvt
		 ORDER BY Nombres;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #VENTAS_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT Colaborador AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #VENTAS_PIVOT';
						
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SET @SQL_TEXT = 'SELECT '''' AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #VENTAS_PIVOT';
						
		EXEC sp_executesql @SQL_TEXT;
	END

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerReporteSobrantes')
BEGIN
	DROP PROCEDURE ObtenerReporteSobrantes;
END 
GO

CREATE PROCEDURE ObtenerReporteSobrantes
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
		@TEXTO_JSON VARCHAR(MAX) = '';

DECLARE @SobrantesPorMes
	TABLE (
		IdMes INT,
		IdColaborador INT,
		Sobrante NUMERIC(10, 2),
		Nombres VARCHAR(100)
	);

BEGIN

	IF EXISTS ( SELECT 1 
				  FROM FaltantesSobrantes 
				 WHERE MONTH(FechaIngreso) BETWEEN @MesInicio AND @MesFin
				   AND YEAR(FechaIngreso) = @Anio )
	BEGIN
	
		
		INSERT INTO @SobrantesPorMes
			 SELECT MONTH(FechaIngreso) AS IdMes,
					COL.IdColaborador,
					SUM(Sobrante) AS Sobrante,
					MAX(COL.Nombres) AS Nombres
			   FROM Colaborador COL
			   LEFT JOIN FaltantesSobrantes FS
				 ON FS.IdColaborador = COL.IdColaborador
			  WHERE YEAR(FechaIngreso) = @Anio
				AND COL.Estado = 'A'
				AND MONTH(FechaIngreso) BETWEEN @MesInicio AND @MesFin
			  GROUP BY COL.IdColaborador, MONTH(FechaIngreso);
	
		INSERT INTO @SobrantesPorMes
			SELECT IdMes, IdColaborador, 0, ( SELECT Nombres FROM Colaborador C WHERE C.IdColaborador = Origen.IdColaborador)
			  FROM ( SELECT Mes.IdMes, COL.IdColaborador
					   FROM Mes, Colaborador COL
					  WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin
						AND COL.Estado = 'A'

					 EXCEPT
							
					 SELECT IdMes, IdColaborador
					   FROM @SobrantesPorMes ) AS Origen;

	
		WITH DataPoints
		AS
		( 
			 SELECT '{ y: ' + CAST(SUM(Sobrante) AS VARCHAR) + ', label: ''' + 
					MAX(Nombres) + ''' }' AS Texto,
					IdColaborador,
					IdMes
			   FROM @SobrantesPorMes
			  GROUP BY IdColaborador, IdMes
	   )


		SELECT @TEXTO_JSON = @TEXTO_JSON + 
			   '{ type: ''stackedColumn'', toolTipContent: "{label}<br/><span style=''color: {color};''><strong>{name}</strong></span>: S/. {y}", ' +
			   'name: ''' + Mes.NombreMes + ''', showInLegend: ''false'', dataPoints: [' +
			   SUBSTRING(DAT.Texto, 1, LEN(DAT.Texto) - 1) + ' ] }, '
		  FROM ( SELECT DISTINCT IdMes
				   FROM DataPoints ) M
			   CROSS APPLY
			   ( SELECT Texto + ', ' 
				   FROM DataPoints AS D 
				  WHERE M.IdMes = D.IdMes
					FOR XML PATH('') ) DAT (Texto)
		  JOIN Mes
			ON Mes.IdMes = M.IdMes
		 WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin;


		SET @TEXTO_JSON = @TEXTO_JSON +' { type: "line", dataPoints: [ ';


		WITH SobrantePorMes 
		AS
		(
			 SELECT SUM(SPM.Sobrante) AS SobranteDelMes,
					MAX(SPM.Nombres) AS Nombre,
					Mes.IdMes,
					SPM.IdColaborador
			   FROM @SobrantesPorMes SPM
			   JOIN Mes
				 ON Mes.IdMes = SPM.IdMes
			  WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin
			  GROUP BY SPM.IdColaborador, Mes.IdMes
		)

		 SELECT @TEXTO_JSON = @TEXTO_JSON + 
				'{ y: ' + CAST( SUM(SobranteDelMes) AS VARCHAR ) +
				', label: ''' + MAX(Nombre) + ''' }, '
		   FROM SobrantePorMes
		  GROUP BY IdColaborador;

		SELECT '{ theme: ''theme2'', title:{ text: '''' }, data: [ ' +
			   SUBSTRING(@TEXTO_JSON, 1, LEN(@TEXTO_JSON) - 1) + ' ] } ] }';
	
	END
	ELSE
	BEGIN
	
		SELECT @TEXTO_JSON;
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerResumenDeSobrantes')
BEGIN
	DROP PROCEDURE ObtenerResumenDeSobrantes;
END 
GO


CREATE PROCEDURE ObtenerResumenDeSobrantes
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #SOBRANTE_PIVOT 
(
	IdColaborador INT,
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);

BEGIN

	INSERT INTO #SOBRANTE_PIVOT
		SELECT IdColaborador, [1] AS Enero, [2] AS Febrero, 
			   [3] AS Marzo, [4] AS Abril, [5] AS Mayo, 
			   [6] AS Junio, [7] AS Julio, [8] AS Agosto, 
			   [9] AS Setiembre, [10] AS Octubre, 
			   [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT IdColaborador, 
						MONTH(FechaIngreso) AS MesIngreso, 
						SUM(Sobrante) AS Sobrante
				   FROM FaltantesSobrantes
				   JOIN Mes 
					 ON MONTH(FechaIngreso) = Mes.IdMes
				  WHERE MONTH(FechaIngreso) BETWEEN @MesInicio AND @MesFin
				    AND YEAR(FechaIngreso) = @Anio
				  GROUP BY MONTH(FechaIngreso), IdColaborador ) AS TablaOrigen
		 PIVOT ( SUM(Sobrante) FOR MesIngreso 
						IN ([1], [2], [3], [4], [5], [6], 
							[7], [8], [9], [10], [11], [12] ) ) AS TablaPivot;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #SOBRANTE_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT COL.Nombres AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #SOBRANTE_PIVOT PVT ' +
						' JOIN Colaborador COL ' +
						'   ON PVT.IdColaborador = COL.IdColaborador ' +
						'WHERE COL.Estado = ''A''';
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SELECT IdColaborador 
		  FROM #SOBRANTE_PIVOT;
	END

	DROP TABLE #SOBRANTE_PIVOT;

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerVentasTotales')
BEGIN
	DROP PROCEDURE ObtenerVentasTotales;
END 
GO


CREATE PROCEDURE ObtenerVentasTotales
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #VENTAS_PIVOT 
(
	IdColaborador INT,
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);

BEGIN

	INSERT INTO #VENTAS_PIVOT
		SELECT IdColaborador, [1] AS Enero, [2] AS Febrero, 
			   [3] AS Marzo, [4] AS Abril, [5] AS Mayo, 
			   [6] AS Junio, [7] AS Julio, [8] AS Agosto, 
			   [9] AS Setiembre, [10] AS Octubre, 
			   [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT VEN.IdColaborador, 
						VEN.IdMes AS MesIngreso, 
						SUM(VentasContado) + SUM(VentasCredito) AS VentasTotales
				   FROM Ventas VEN
				   JOIN Mes 
					 ON VEN.IdMes = Mes.IdMes
				  WHERE VEN.IdMes BETWEEN @MesInicio AND @MesFin
				    AND VEN.Anio = @Anio
				  GROUP BY VEN.IdMes, IdColaborador ) AS TablaOrigen
		 PIVOT ( SUM(VentasTotales) FOR MesIngreso
						IN ([1], [2], [3], [4], [5], [6], 
							[7], [8], [9], [10], [11], [12] ) ) AS TablaPivot;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #VENTAS_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT COL.Nombres AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #VENTAS_PIVOT PVT ' +
						' JOIN Colaborador COL ' +
						'   ON PVT.IdColaborador = COL.IdColaborador ';
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SELECT IdColaborador 
		  FROM #VENTAS_PIVOT;
	END

	DROP TABLE #VENTAS_PIVOT

END
GO




IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerVentasContado')
BEGIN
	DROP PROCEDURE ObtenerVentasContado;
END 
GO


CREATE PROCEDURE ObtenerVentasContado
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #VENTAS_PIVOT 
(
	IdColaborador INT,
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);

BEGIN

	INSERT INTO #VENTAS_PIVOT
		SELECT IdColaborador, [1] AS Enero, [2] AS Febrero, 
			   [3] AS Marzo, [4] AS Abril, [5] AS Mayo, 
			   [6] AS Junio, [7] AS Julio, [8] AS Agosto, 
			   [9] AS Setiembre, [10] AS Octubre, 
			   [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT VEN.IdColaborador, 
						VEN.IdMes AS MesIngreso, 
						SUM(VentasContado) AS VentasTotales
				   FROM Ventas VEN
				   JOIN Mes 
					 ON VEN.IdMes = Mes.IdMes
				  WHERE VEN.IdMes BETWEEN @MesInicio AND @MesFin
				    AND VEN.Anio = @Anio
				  GROUP BY VEN.IdMes, IdColaborador ) AS TablaOrigen
		 PIVOT ( SUM(VentasTotales) FOR MesIngreso
						IN ([1], [2], [3], [4], [5], [6], 
							[7], [8], [9], [10], [11], [12] ) ) AS TablaPivot;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #VENTAS_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT COL.Nombres AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #VENTAS_PIVOT PVT ' +
						' JOIN Colaborador COL ' +
						'   ON PVT.IdColaborador = COL.IdColaborador ';
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SELECT IdColaborador 
		  FROM #VENTAS_PIVOT;
	END

	DROP TABLE #VENTAS_PIVOT

END
GO



IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerVentasCredito')
BEGIN
	DROP PROCEDURE ObtenerVentasCredito;
END 
GO


CREATE PROCEDURE ObtenerVentasCredito
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@SQL_TEXT NVARCHAR(MAX) = '';

CREATE TABLE #VENTAS_PIVOT 
(
	IdColaborador INT,
	Enero NUMERIC(15, 2), 
	Febrero NUMERIC(15, 2), 
	Marzo NUMERIC(15, 2), 
	Abril NUMERIC(15, 2), 
	Mayo NUMERIC(15, 2), 
	Junio NUMERIC(15, 2), 
	Julio NUMERIC(15, 2), 
	Agosto NUMERIC(15, 2), 
	Setiembre NUMERIC(15, 2), 
	Octubre NUMERIC(15, 2), 
	Noviembre NUMERIC(15, 2), 
	Diciembre NUMERIC(15, 2)
);

BEGIN

	INSERT INTO #VENTAS_PIVOT
		SELECT IdColaborador, [1] AS Enero, [2] AS Febrero, 
			   [3] AS Marzo, [4] AS Abril, [5] AS Mayo, 
			   [6] AS Junio, [7] AS Julio, [8] AS Agosto, 
			   [9] AS Setiembre, [10] AS Octubre, 
			   [11] AS Noviembre, [12] AS Diciembre
		  FROM ( SELECT VEN.IdColaborador, 
						VEN.IdMes AS MesIngreso, 
						SUM(VentasCredito) AS VentasTotales
				   FROM Ventas VEN
				   JOIN Mes 
					 ON VEN.IdMes = Mes.IdMes
				  WHERE VEN.IdMes BETWEEN @MesInicio AND @MesFin
				    AND VEN.Anio = @Anio
				  GROUP BY VEN.IdMes, IdColaborador ) AS TablaOrigen
		 PIVOT ( SUM(VentasTotales) FOR MesIngreso
						IN ([1], [2], [3], [4], [5], [6], 
							[7], [8], [9], [10], [11], [12] ) ) AS TablaPivot;

	 SELECT @SQL_TEXT = @SQL_TEXT +
			(CASE WHEN MAX(Enero) IS NOT NULL THEN @SQL_TEXT + 'Enero,' ELSE '' END) + 
			(CASE WHEN MAX(Febrero) IS NOT NULL THEN @SQL_TEXT + 'Febrero,' ELSE '' END) + 
			(CASE WHEN MAX(Marzo) IS NOT NULL THEN @SQL_TEXT + 'Marzo,' ELSE '' END) + 
			(CASE WHEN MAX(Abril) IS NOT NULL THEN @SQL_TEXT + 'Abril,' ELSE '' END) + 
			(CASE WHEN MAX(Mayo) IS NOT NULL THEN @SQL_TEXT + 'Mayo,' ELSE '' END) + 
			(CASE WHEN MAX(Junio) IS NOT NULL THEN @SQL_TEXT + 'Junio,' ELSE '' END) + 
			(CASE WHEN MAX(Julio) IS NOT NULL THEN @SQL_TEXT + 'Julio,' ELSE '' END) + 
			(CASE WHEN MAX(Agosto) IS NOT NULL THEN @SQL_TEXT + 'Agosto,' ELSE '' END) + 
			(CASE WHEN MAX(Setiembre) IS NOT NULL THEN @SQL_TEXT + 'Setiembre,' ELSE '' END) + 
			(CASE WHEN MAX(Octubre) IS NOT NULL THEN @SQL_TEXT + 'Octubre,' ELSE '' END) + 
			(CASE WHEN MAX(Noviembre) IS NOT NULL THEN @SQL_TEXT + 'Noviembre,' ELSE '' END) + 
			(CASE WHEN MAX(Diciembre) IS NOT NULL THEN @SQL_TEXT + 'Diciembre,' ELSE '' END)  
	   FROM #VENTAS_PIVOT;

	IF (LEN(@SQL_TEXT) > 0)
	BEGIN
		SET @SQL_TEXT = 'SELECT COL.Nombres AS Grifero,' + 
						SUBSTRING(@SQL_TEXT, 1, LEN(@SQL_TEXT) - 1) + 
						' FROM #VENTAS_PIVOT PVT ' +
						' JOIN Colaborador COL ' +
						'   ON PVT.IdColaborador = COL.IdColaborador ';
		EXEC sp_executesql @SQL_TEXT;
	END
	ELSE
	BEGIN
		SELECT IdColaborador 
		  FROM #VENTAS_PIVOT;
	END

	DROP TABLE #VENTAS_PIVOT

END
GO



IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerDiasLaboradosPorMes')
BEGIN
	DROP PROCEDURE ObtenerDiasLaboradosPorMes;
END 
GO

CREATE PROCEDURE ObtenerDiasLaboradosPorMes
	@IdMes INTEGER
AS
BEGIN

	SELECT COL.IdColaborador,
		   DIA.IdMes,
		   COL.Nombres + ' ' + COL.Apellidos AS Nombre,
		   ISNULL(DIA.CantidadDias, 0) AS CantidadDias,
		   ISNULL(DIA.Anio, YEAR(GETDATE())) AS Anio
	  FROM Colaborador COL
	  LEFT JOIN DiasLaborados DIA
	    ON COL.IdColaborador = DIA.IdColaborador
	   AND DIA.IdMes = @IdMes
	   AND DIA.Anio = YEAR(GETDATE())
	 WHERE COL.Estado = 'A';
	
END
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarActualizarDiasLaborados')
BEGIN
	DROP PROCEDURE InsertarActualizarDiasLaborados;
END 
GO


CREATE PROCEDURE InsertarActualizarDiasLaborados 
	@IdColaborador INTEGER,
	@IdMes INTEGER,
	@Anio INTEGER,
	@CantidadDias INTEGER
AS
BEGIN

	UPDATE DiasLaborados
	   SET CantidadDias = @CantidadDias
	 WHERE IdColaborador = @IdColaborador
	   AND IdMes = @IdMes
	   AND Anio = @Anio;
	
	IF @@ROWCOUNT = 0
	BEGIN

		INSERT INTO DiasLaborados (IdColaborador, IdMes, Anio, CantidadDias)
			VALUES (@IdColaborador, @IdMes, @Anio, @CantidadDias);
	
	END
END
GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerGratificacionPorMes')
BEGIN
	DROP PROCEDURE ObtenerGratificacionPorMes;
END 
GO

CREATE PROCEDURE ObtenerGratificacionPorMes 
	@IdMes INTEGER,
	@Anio INTEGER
AS

DECLARE
	@GRATIFICACION TABLE
	(
		IdColaborador INTEGER,
		IdMes INTEGER,
		Anio INTEGER,
		Nombre VARCHAR(500),
		Coeficiente NUMERIC(15, 10),
		Ponderacion NUMERIC(15, 10)
	);

BEGIN

	WITH TotalErrores 
	AS
	(
		SELECT ERR.IdColaborador, 
			   MONTH(FechaIngreso) AS Mes, 
			   SUM(Cantidad) AS ErroresPorMes
		  FROM ErroresPorColaborador ERR
		 WHERE MONTH(FechaIngreso) = @IdMes
		   AND YEAR(FechaIngreso) = @Anio
		 GROUP BY ERR.IdColaborador, MONTH(FechaIngreso)
	)

	INSERT INTO @GRATIFICACION (IdColaborador, IdMes, Anio, Nombre, Coeficiente, Ponderacion)
		SELECT COL.IdColaborador, 
			   DIA.IdMes,
			   DIA.Anio,
			   COL.Apellidos + ' ' + COL.Nombres AS Nombre,
			   ((ERR.ErroresPorMes * 1.0 / DIA.CantidadDias * 1.0) / SUM(ERR.ErroresPorMes * 1.0 / DIA.CantidadDias  * 1.0) OVER() ) * 0.1 AS Coeficiente,
			   CAST(DEP.DespachosNoBoleteado * 1.0 / DEP.DespachosEmitidos * 1.0 AS NUMERIC(15, 10)) * ( CAST((SUM(DEP.DespachosNoBoleteado * 1.0) OVER() ) / (SUM(DEP.DespachosEmitidos * 1.0) OVER()) AS NUMERIC(15, 10))) AS Ponderacion
		  FROM Colaborador COL
		  LEFT JOIN TotalErrores ERR
			ON ERR.IdColaborador = COL.IdColaborador
		  JOIN DiasLaborados DIA
			ON DIA.IdColaborador = ERR.IdColaborador
		   AND DIA.IdMes = @IdMes
		   AND DIA.Anio = @Anio 
		  LEFT JOIN Despachos DEP
			ON DEP.IdColaborador = ERR.IdColaborador
		   AND DEP.IdMes = @IdMes
		   AND DEP.Anio = @Anio 
		 WHERE COL.Estado = 'A';
	
	SELECT IdColaborador,
		   IdMes,
		   Anio,
		   Nombre,
		   Coeficiente,
		   Ponderacion,
		   Coeficiente + Ponderacion AS Sumatoria,
		   1 - (Coeficiente + Ponderacion) AS Resultado,
		   (1 - (Coeficiente + Ponderacion)) * 100 / SUM(1 - (Coeficiente + Ponderacion)) OVER() AS Porcentaje
	  FROM @GRATIFICACION;
	   
	
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerReporteVentasAlCredito')
BEGIN
	DROP PROCEDURE ObtenerReporteVentasAlCredito;
END 
GO

CREATE PROCEDURE ObtenerReporteVentasAlCredito
	@Anio INTEGER,
	@MesInicio INTEGER,
	@MesFin INTEGER
AS

DECLARE 
	@TEXTO_JSON VARCHAR(MAX) = '';

BEGIN

	IF EXISTS ( SELECT 1 
				  FROM Ventas
				 WHERE IdMes BETWEEN @MesInicio AND @MesFin )
	BEGIN
	
		WITH DataPoints
		AS ( SELECT '{ y: ' + CAST(SUM(VentasCredito) AS VARCHAR) + ', label: ''' + 
						MAX(COL.Nombres) + ''' }' AS Texto,
						COL.IdColaborador,
						VEN.IdMes
				   FROM Ventas VEN
				   JOIN Colaborador COL
					 ON VEN.IdColaborador = COL.IdColaborador
				  WHERE VEN.Anio = @Anio
					AND COL.Estado = 'A'
				  GROUP BY COL.IdColaborador, VEN.IdMes 
			   )

		SELECT @TEXTO_JSON = @TEXTO_JSON + 
			   '{ type: ''stackedColumn'', toolTipContent: "{label}<br/><span style=''color: {color};''><strong>{name}</strong></span>: S/. {y}", ' +
			   'name: ''' + Mes.NombreMes + ''', showInLegend: ''false'', dataPoints: [' +
			   SUBSTRING(DAT.Texto, 1, LEN(DAT.Texto) - 1) + ' ] }, '
		  FROM ( SELECT DISTINCT IdMes
				   FROM DataPoints ) M
			   CROSS APPLY
			   ( SELECT Texto + ', ' 
				   FROM DataPoints AS D 
				  WHERE M.IdMes = D.IdMes
					FOR XML PATH('') ) DAT (Texto)
		  JOIN Mes
			ON Mes.IdMes = M.IdMes
		 WHERE Mes.IdMes BETWEEN @MesInicio AND @MesFin;


		SET @TEXTO_JSON = @TEXTO_JSON +' { type: "line", dataPoints: [ ';


		WITH VentasPorMes 
		AS
		(
			 SELECT SUM(VEN.VentasCredito) AS Total,
					MAX(COL.Nombres) AS Nombre,
					Mes.IdMes,
					COL.IdColaborador
			   FROM Ventas VEN
			   JOIN Colaborador COL
				 ON VEN.IdColaborador = COL.IdColaborador
			   JOIN Mes
				 ON Mes.IdMes = VEN.IdMes
			  WHERE Anio = @Anio
				AND COL.Estado = 'A'
				AND Mes.IdMes BETWEEN @MesInicio AND @MesFin
			  GROUP BY COL.IdColaborador, Mes.IdMes
		)

		 SELECT @TEXTO_JSON = @TEXTO_JSON + 
				'{ y: ' + CAST( SUM(Total) AS VARCHAR ) +
				', label: ''' + MAX(Nombre) + ''' }, '
		   FROM VentasPorMes 
		  WHERE Total > 0
		  GROUP BY IdColaborador;
		
		SELECT '{ theme: ''theme2'', title:{ text: '''' }, data: [ ' +
			   SUBSTRING(@TEXTO_JSON, 1, LEN(@TEXTO_JSON) - 1) + ' ] } ] }';
	
	END
	ELSE
	BEGIN
	
		SELECT @TEXTO_JSON;
	
	END
END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ListadoFaltantesSobrantes')
BEGIN
	DROP PROCEDURE ListadoFaltantesSobrantes;
END 
GO


CREATE PROCEDURE ListadoFaltantesSobrantes
	@IdColaborador INTEGER,
	@IdMes INTEGER,
	@Anio INTEGER
AS
BEGIN

	SELECT COL.IdColaborador,
		   COL.Nombres + ' ' + COL.Apellidos AS NombreColaborador,
		   FS.FechaIngreso,
		   FS.Faltante,
		   FS.Sobrante
	  FROM FaltantesSobrantes FS
	  JOIN Colaborador COL
		ON FS.IdColaborador = COL.IdColaborador
	 WHERE COL.Estado = 'A'
	   AND COL.IdColaborador = @IdColaborador
	   AND MONTH(FS.FechaIngreso) = @IdMes
	   AND YEAR(FS.FechaIngreso) = @Anio;

END
GO


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'ObtenerAnios')
BEGIN
	DROP PROCEDURE ObtenerAnios;
END 
GO


CREATE PROCEDURE ObtenerAnios
AS
BEGIN
	
	SELECT 2014
	UNION
	SELECT 2015
	
END
GO




/*IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'InsertarActualizarErrores')
BEGIN
	DROP PROCEDURE InsertarActualizarErrores;
END 
GO


CREATE PROCEDURE [Nombre]
	@[PARAMETROS] INTEGER
AS
BEGIN
	
END
GO
*/

